﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Utils;
using Settings;
using System.Data.SqlClient;
using System.Data;
using PanasonicWarranty.Business;

namespace PanasonicWarranty.Business
{
    public partial class pa_Agency : BaseClass
    {
        #region private field
        DBWarrantyDataContext dbcontext = new DBWarrantyDataContext(AppSetting.ConnectionString);
        #endregion        

        #region Singleton Pattern
        private static object _lock = new object();
        private static List<pa_Agency> _instance;
        public static List<pa_Agency> GetInstance()
        {
            lock (_lock)
            {
                if (_instance == null)
                {
                    lock (_lock)
                    {
                        using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
                        {
                            _instance = context.pa_Agencies.ToList();
                        }
                    }
                }
            }
            return _instance;
        }
        public static void ReloadData()
        {
            lock (_lock)
            {
                using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
                {
                    _instance = context.pa_Agencies.ToList();
                }
            }
        }
        #endregion Singleton Pattern

        public static pa_Agency GetByID(int id)
        {
            return GetInstance().FirstOrDefault(p => p.AgencyID == id);
        }

        public static string GetAgencyName(object id)
        {
            try
            {
                return GetByID(Convert.ToInt32(id)).AgencyName;
            }
            catch
            {
                return "";
            }
        }

        public static string GetPhone(object id)
        {
            try
            {
                var listPhone = pa_AgencyPhone.GetInstance().Where(p => p.AgencyID == Convert.ToInt32(id));
                if (listPhone.Count() == 0) return string.Empty;
                return string.Join(",", listPhone.Select(p => p.Phone));
            }
            catch
            {
                return "";
            }
        }

        public static string GetUser(object id)
        {
            try
            {
                using (var context = new DBSysDataContext(AppSetting.ConnectionString))
                {
                    List<SysUser> listUser = context.SysUsers.Where(p => 
                                p.AgencyID == Convert.ToInt32(id) &&
                                p.Disabled == false &&
                                p.Deleted == false
                            ).ToList();
                    if (listUser.Count == 0) return string.Empty;
                    return string.Join(",", listUser.Select(p => p.UserName));
                }
            }
            catch
            {
                return "";
            }
        }

        public static int GetMaxOrd(int? parentID)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                int? maxOrd = null;
                if (parentID == null)
                {
                    maxOrd = context.pa_Agencies.Where(p => p.ParentID == null).Max(p => p.Ord);
                }
                else
                {
                    maxOrd = context.pa_Agencies.Where(p => p.ParentID == parentID).Max(p => p.Ord);
                }
                return (maxOrd ?? 0) + 1;
            }
        }

        public static void Delete(int id)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var obj = context.pa_Agencies.FirstOrDefault(p => p.AgencyID == id);
                if (obj != null)
                {
                    if (CheckAbilityDelete(id))
                        context.pa_Agencies.DeleteOnSubmit(obj);
                    else
                        throw new CustomException("Không thể xóa đại lý này vì đã phát sinh các nghiệp vụ liên quan");
                }
                context.SubmitChanges();
            }
        }

        private static bool CheckAbilityDelete(int agencyId)
        {
            return (bool)ExecuteScalar("usp_pa_Agency_CheckAbilityDelete", CommandType.StoredProcedure, new SqlParameter("@AgencyID", agencyId));
        }

        public static List<pa_Agency> GetAll()
        {
            return GetInstance().OrderBy(p => p.Level).ToList();
        }

        public static DataTable GetDataExport()
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(AppSetting.ConnectionString))
            {
                dt.Load(SqlHelper.ExecuteReader(conn,CommandType.StoredProcedure,"usp_Agency_GetExportData"));
            }
            return dt;
        }

        public static string GetCaption(string columnName)
        {
            switch (columnName)
            {
                case "AgencyName":
                    return "Tên đại lý";
                case "Address":
                    return "Địa chỉ";
                case "Phone":
                    return "Điện thoại";
                case "AgencyParentName":
                    return "Đại lý cấp cha";
                case "GHBH":
                    return "Được phép GHBH";
                case "Ord":
                    return "STT";
                case "Disabled":
                    return "Ngưng SD";
            }
            return "";
        }

        public static DataTable GetByFullTextSearch(string agencyName)
        {
            return ExecuteReader("usp_pa_Agency_GetByFullTextSearch", CommandType.StoredProcedure, new SqlParameter("@AgencyName", agencyName));
        }

        public static List<pa_Agency> GetAll(int parentId)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var obj = context.pa_Agencies.FirstOrDefault(p => p.AgencyID == parentId);
                if (obj == null) 
                    return null;
                return context.pa_Agencies.Where(p => p.Level.IndexOf(obj.Level) == 0).ToList();
            }
        }

        public static void TransformLevel(ref List<pa_Agency> source)
        {
            if (source.Count == 1)
                return;
            var len = source.First().Level.Length;
            if (len == 5)
                return;
            foreach (var item in source)
            {
                item.Level = item.Level.Substring(len - 5);
            }
            
        }

        public string GetPhoneNumber()
        {
            List<pa_AgencyPhone> listPhone = pa_AgencyPhone.GetInstance().Where(p => p.AgencyID == this.AgencyID).ToList();
            if(listPhone.Count == 0) return string.Empty;
            return string.Join(",", listPhone.Select(p => p.Phone));
        }

        public void SavePhoneNumber(string phonelist)
        {
            try
            {
                string[] phone = phonelist.Split(new string[] { ",", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
                for (int i = 0; i < phone.Length; i++)
                    phone[i] = phone[i].ToNormalPhoneNumber();
                CheckPhoneNumber(phone);
                pa_AgencyPhone.Delete(this.AgencyID);
                foreach(string item in phone.Distinct())
                {
                    pa_AgencyPhone objPhone = new pa_AgencyPhone()
                    {
                        AgencyID = this.AgencyID,
                        Phone = item
                    };
                    objPhone.Create();
                }
            }
            catch (CustomException e)
            {
                throw e;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        private void CheckPhoneNumber(string[] phoneList)
        {
            if (!OperatorPrefixCode.ValidatePhoneNumber(phoneList))
                throw new CustomException("Số điện thoại không hợp lệ");
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                if(dbcontext.pa_AgencyPhones.Any(a => a.AgencyID != this.AgencyID && phoneList.Contains(a.Phone)))
                    throw new CustomException("Số điện thoại đã bị trùng ở đại lý khác");
            }
        }

        public static void SetFullControl(bool check, int agencyId)
        {
            try
            {
                ExecuteQuery("UPDATE pa_Agency SET FullControl = @FullControl WHERE AgencyID = @AgencyID",
                    new SqlParameter("@FullControl", check),
                    new SqlParameter("@AgencyID", agencyId));
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static int ImportExcel(string filepath)
        {
            SqlConnection conn = null;
            int rowaffected = 0;
            try
            {
                conn = new SqlConnection(AppSetting.ConnectionString);
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    //Disable trigger
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "ALTER TABLE pa_Agency DISABLE TRIGGER tg_Update_AgencyCode");
                    using (DataTable dt = ExcelHelper.ReadFromFile(filepath))
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            Insert(trans, GetStringValue(row[0]), GetStringValue(row[1]), GetStringValue(row[2]), GetStringValue(row[3]), GetStringValue(row[4]), GetStringValue(row[5]), GetBoolValue(row[6]));
                            rowaffected++;
                        }
                    }
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "ALTER TABLE pa_Agency ENABLE TRIGGER tg_Update_AgencyCode");
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, "UPDATE pa_Agency SET AgencyCode = AgencyID WHERE AgencyCode <> CAST(AgencyID AS VARCHAR(10))");
                    trans.Commit();
                }
                return rowaffected;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    SqlHelper.ExecuteNonQuery(conn, CommandType.Text, "IF(EXISTS(SELECT NULL AS [EMPTY] FROM sys.triggers s WHERE s.name = 'tg_Update_AgencyCode' AND s.is_disabled = 1)) ALTER TABLE pa_Agency ENABLE TRIGGER tg_Update_AgencyCode");
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        private static string GetStringValue(object obj)
        {
            if (obj is string)
                return (string)obj;
            return string.Empty;
        }

        private static bool GetBoolValue(object obj)
        {
            bool retValue = false;
            bool.TryParse(GetStringValue(obj), out retValue);
            return retValue;
        }

        private static void Insert(SqlTransaction trans, string madaily, string madailycha, string tendaily,
            string diachi, string sodienthoai, string email, bool ghbh)
        {
            if (OperatorPrefixCode.ValidatePhoneNumber(ref sodienthoai))
            {
                try
                {
                    //Kiểm tra số điện thoại đăng ký trùng
                    if ((bool)SqlHelper.ExecuteScalar(trans, CommandType.Text, "IF(EXISTS(SELECT NULL AS [EMPTY] FROM pa_AgencyPhone WHERE Phone = @Phone))	SELECT CAST(1 AS BIT) ELSE SELECT CAST(0 AS BIT)", new SqlParameter("@Phone", sodienthoai)))
                    {
                        throw new Exception("Số điện thoại " + sodienthoai + " đã được đăng ký trong hệ thống. Vui lòng kiểm tra lại!");
                    }
                    SqlHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "usp_pa_Agency_Insert",
                            new SqlParameter("@AgencyCode", madaily),
                            new SqlParameter("@ParentAgencyCode", string.IsNullOrWhiteSpace(madailycha) ? (object)DBNull.Value : madailycha),
                            new SqlParameter("@AgencyName", tendaily),
                            new SqlParameter("@Address", diachi),
                            new SqlParameter("@Phone", sodienthoai),
                            new SqlParameter("@Password", sodienthoai.ToMD5Hash()),
                            new SqlParameter("@Email", email),
                            new SqlParameter("@GHBH", ghbh),
                            new SqlParameter("@DefautParentID", 18),
                            new SqlParameter("@DefaultSysGroupID", 16)
                        );
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
            else
            {
                throw new Exception("Số điện thoại " + sodienthoai + " không hợp lệ. Vui lòng kiểm tra lại!");
            }
        }
    }
}
